Skip to main content

UNION

Overview​

Use UNION keyword to combine several queries together. As a result, you get a dataset with fields returned by all included in the UNION queries chronologically arranged by their timestamps. UNION can be applied to querying from the same or different streams.

Polymorphic Dataset​

You get a polymorphic dataset as a result of a UNION in case it combines messages of different classes.

Examples with UNION keyword
-- UNION of these two queries will return a polymorphic dataset with fields of both classes TradeMessage and BestBidOfferMessage
SELECT 
  trade.price AS "price", 
  trade.size AS "size"
TYPE "TradeMessage"
FROM kraken 
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT
  bbo[side == ASK].price AS "offerPrice", 
  bbo[side == ASK].size AS "offerSize", 
  bbo[side == BID].price AS "bidPrice", 
  bbo[side == BID].size AS "bidSize"
TYPE "BestBidOfferMessage"
FROM kraken 
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo
-- UNION of these two queries will return a polymorphic dataset with fields of both classes Bar1min and Bar5min
WITH 
  entries[THIS IS TradeEntry] AS 'entries'
SELECT
  sum{}(sum(entries.size)) AS 'volume',
  first{}(entries[0].price) AS 'open',
  last{}(entries[-1].price) AS 'close',
  max{}(max(entries.price)) AS 'high',
  min{}(min(entries.price)) AS 'low'
TYPE "Bar1min"
FROM binance
OVER TIME(1m)
WHERE symbol == 'BTC/USDT' AND notEmpty(entries)

UNION

WITH 
  entries[THIS IS TradeEntry] AS 'entries'
SELECT
  sum{}(sum(entries.size)) AS 'volume',
  first{}(entries[0].price) AS 'open',
  last{}(entries[-1].price) AS 'close',
  max{}(max(entries.price)) AS 'high',
  min{}(min(entries.price)) AS 'low'
TYPE "Bar5min"
FROM binance
OVER TIME(5m)
WHERE symbol == 'BTC/USDT' AND notEmpty(entries)
tip

You can also use RECORD ... TYPE ... WHEN construction to produce a polymorphic data set in a single query.

Fixed-Type Dataset​

In case UNION combines messages of the same class, you get a fixed type dataset with fields from all the involved queries merged in one class. In case of identical column names, UNION places results in one column chronologically.

tip

It is not possible to UNION two fields with the same Name but different data types. For example, field Price of integer and string data types cannot be combined by UNION in one field. 

-- UNION of these two queries will return a fixed type dataset with fields from both queries' results combined in one TradeAndBBO class.
SELECT 
  trade.price AS "price", 
  trade.size AS "size"
TYPE "TradeAndBBO"
FROM kraken 
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT 
  bbo[side == ASK].price AS "offerPrice", 
  bbo[side == ASK].size AS "offerSize", 
  bbo[side == BID].price AS "bidPrice", 
  bbo[side == BID].size AS "bidSize"
TYPE "TradeAndBBO"
FROM kraken 
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo

Casting to a Common Type​

You can take messages of two different types, lets say type "TradeMessage" and type "BestBidOfferMessage" and UNION them under one common message type for example type "NewType". As the result of such a query, you will get a fixed type dataset with fields from both classes combined in one new class NewType.

Stream UNION​

caution

This feature is available starting from QQL 5.6.

If you want to perform the same action on different streams, you can use UNION in a single query. This way, you can have one message source subscribed to several streams.

REVERSE and LIVE queries are also supported with UNIONS. Refer to Queries to learn more.

Restrictions

  • Message types (classes) with identical names in streams the UNION applies to, must be binary identical (have identical number of fields, names, types, and order).
  • Union streams must be enclosed in brackets: select * from (stream1 union stream2).
# Aggregation of bars from three streams

with 
  this.entries[this is L1Entry][0] as trade
select
  trade.exchangeId as exchange,
  max{}(trade.price) as high,
  min{}(trade.price) as low,
  first{}(trade.price) as open,
  last{}(trade.price) as close
from (binance UNION kraken UNION bitfinex)
over time(5s)
where trade != null and symbol == 'BTC/USD'